﻿-- 客户列表
drop table custom_list;
create table custom_list(
  custom_id number(10) not null primary key ,
  custom_name varchar2(255) not null,
  follow_up_status number(1) not null,
  channel varchar(255) default ' ' ,
  stage number(1) default 0 ,
  address varchar2(255) default ' ' ,
  follow_up_person varchar(32) default 'admin' ,
  create_by varchar2(32) default 'admin' ,
  create_date date ,
  create_time date  ,
  update_by varchar2(32) default ' ' ,
  update_time varchar2(32) default ' ' ,
  is_deleted number(1) default 0 
);

comment on table custom_list is '客户列表';
comment on column custom_list.custom_id is '客户列表主题ID';
comment on column custom_list.custom_name is '企业名称';
comment on column custom_list.follow_up_status is '跟进状态 1已跟进 2待跟进 3未跟进';
comment on column custom_list.channel is '渠道： 0地推 1 官网 2客户介绍  3广告 4搜索引擎 5熟人介绍 6社交推广';
comment on column custom_list.stage is '阶段, 0:未成交客户 1:成交客户 2:多次成交用户 3:公海用户';
comment on column custom_list.address is '地址';
comment on column custom_list.follow_up_person is '跟进人';
comment on column custom_list.create_by is '创建人';
comment on column custom_list.create_date is '创建日期';
comment on column custom_list.create_time is '创建时间';
comment on column custom_list.update_by is '更新人';
comment on column custom_list.update_time is '更新时间';
comment on column custom_list.is_deleted is '删除状态 0 未删除 1 已删除';

insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'++0企业',1,0,'广东广州天河',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'东莞企业',1,1,'123',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'张三公司',1,1,'广东省',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'3344',2,6,'广州',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'3344',2,0,'广州',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'3344',1,0,'广州白云',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'通讯技术有限公司',1,1,'广东东莞',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'合堂有限公司',1,2,'广东省湛江市',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'rabbit',2,6,'广东省潮州市',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'公主之家',2,1,'上海',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'参半有限公司',1,4,'广州市白云区',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'魔石企业',1,6,'福建厦门',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'Samsung',3,1,'韩国',current_date);
insert into custom_list(custom_id,custom_name,follow_up_status,channel,address,create_time)values(seq_custom_list_id.nextval,'FILA',3,0,'广东广州',current_date);


create sequence seq_custom_list_id;
commit;
select * from CUSTOM_LIST 

-- 联系人列表
drop table contacts_list;
create table contacts_list(
  contacts_id number(10) not null primary key ,
  contacts_name varchar2(32) not null , 
  phone char(11) not null,
  sex number(2) not null ,
  duties varchar2(32) default ' ',
  create_by varchar2(32) default 'admin',
  create_time date default sysdate ,
  update_by varchar2(32) default ' ',
  update_time date default sysdate, 
  is_deleted number(2) default 0
);
comment on table contacts_list is '联系人列表';
comment on column contacts_list.contacts_id is '联系人列表主键id';
comment on column contacts_list.contacts_name is '联系人姓名';
comment on column contacts_list.phone is '手机号';
comment on column contacts_list.sex is '性别: 0 男  1 女';
comment on column contacts_list.duties is '职务';
comment on column contacts_list.create_by is '创建人';
comment on column contacts_list.create_time is '创建时间';
comment on column contacts_list.update_by is '更新人';
comment on column contacts_list.update_by is '更新时间';
comment on column contacts_list.is_deleted is '可删除状态,0 未删除 1 已删除'; 

insert into contacts_list(contacts_id,contacts_name,phone,sex,duties) values(seq_contacts_id.nextval,'tmy','12312345678',0,'经理');
insert into contacts_list(contacts_id,contacts_name,phone,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'吴先生','19867935678',0,'副总','1',current_date);
insert into contacts_list(contacts_id,contacts_name,phone,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'呜啦啦','15846321841',0,'副总','1',current_date);
insert into contacts_list(contacts_id,contacts_name,phone,sex,duties,create_by,create_time) values(seq_contacts_id.nextval,'张三','12312312312',0,'副总','1',current_date);


create sequence seq_contacts_id;

commit;
select * from contacts_list
-- rbac
-- ①权限表
drop table permissions;
create table permissions(
  dept_id number(10) not null primary key,
  dept_name varchar2(32) not null,
  create_by varchar2(32) default 'admin',
  create_time date default sysdate,
  update_by varchar2(32) default '' ,
  update_time varchar2(32) default '',
  is_deleted number(2) default 0
);
comment on table permissions is '权限表';
comment on column permissions.dept_id is '权限id';
comment on column permissions.dept_name is '权限名';
comment on column permissions.create_by is '创建人';
comment on column permissions.create_time is '创建时间';
comment on column permissions.update_by is '更新人';
comment on column permissions.update_by is '更新时间';
comment on column permissions.is_deleted is '删除状态,0 未删除 1 已删除'; 

insert into permissions(dept_id,dept_name,create_time) values(seq_permission_id.nextval,'增',sysdate);
insert into permissions(dept_id,dept_name,create_time) values(seq_permission_id.nextval,'删',sysdate);
insert into permissions(dept_id,dept_name,create_time) values(seq_permission_id.nextval,'改',sysdate);
insert into permissions(dept_id,dept_name,create_time) values(seq_permission_id.nextval,'查',sysdate);

create sequence seq_permission_id;

commit;
select * from permissions

-- ②部门表
create table sys_dept(
  dept_id           number(20)      not null  primary key  ,
  ancestors         varchar(50)     default '' ,
  dept_name         varchar(30)     not null   ,
  create_by         varchar(64)     default ' ' ,
  create_time       date   default sysdate  ,
  update_by         varchar(64)     default ' ' ,
  update_time       date  default sysdate 
);

comment on table sys_dept is '部门表';
comment on column sys_dept .dept_id is '部门id';
comment on column sys_dept.ancestors is '祖级列表';
comment on column sys_dept.dept_name is '部门名称';
comment on column sys_dept.create_by is '创建者';
comment on column sys_dept.create_time is '创建时间';
comment on column sys_dept .update_by is '更新者';
comment on column sys_dept .update_time is '更新时间';

insert into sys_dept(dept_id,dept_name) values(seq_sys_dept_id.nextval,'信息研发中心');
insert into sys_dept(dept_id,dept_name) values(seq_sys_dept_id.nextval,'市场部');
insert into sys_dept(dept_id,dept_name) values(seq_sys_dept_id.nextval,'总经办');

create sequence seq_sys_dept_id;

commit;
select * from sys_dept
-- ③用户表
drop table sys_user;
create table sys_user (
  user_id           number(20)      not null  primary key  ,
  user_name         varchar(32)     not null  ,
  user_type         varchar(32)     not null  ,
  user_dept         varchar(32)     default '' ,
  phonenumber       varchar(11)     default '' ,
  status1           number(1)         default '0'  ,
  leave_date        varchar(32)      default ''             ,
  create_by         varchar(64)     default '' ,
  create_time       date         default sysdate    ,
  update_by         varchar(64)     default '' ,
  update_time       date          default sysdate                       
);

comment on table sys_user is '用户信息表';
comment on column sys_user.user_id is '用户ID';
comment on column sys_user.user_name is '用户名称';
comment on column sys_user.user_type is '用户角色';
comment on column sys_user.user_dept is '用户部门';
comment on column sys_user.phonenumber is '手机号码';
comment on column sys_user.status1 is '任职状态';
comment on column sys_user.leave_date is '离职日期';
comment on column sys_user.create_by is '创建者';
comment on column sys_user.create_time is '创建时间';
comment on column sys_user.update_by is '更新者';
comment on column sys_user.update_time is '更新时间';

insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'财务人员001','财务部门',' ',' ',0 ,' ');
insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'销售人员001','销售部门',' ',' ', 0,' ');
insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'仓库人员001','仓库部门','信息研发部 ',' ', 0,' ');
insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'采购人员001','采购部门',' ',' ',0 ,' ');
insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'小曹','系统管理员',' ',' ', 0,' ');
insert into sys_user(user_id,user_name, user_type, user_dept,phonenumber,status1,leave_date ) values(seq_sys_user_id.nextval,'白码','管理员',' ',' ', 0,' ');

create sequence seq_sys_user_id;

commit;
select * from sys_user


create table erp_role (
  role_id           NUMBER(20)      not null,
  role_name         varchar2(30)     not null,
  role_key          varchar2(100)    not null  ,
  status            char(1)  default '0' ,
  del_flag          char(1)         default '0',
  create_by         varchar2(64)     default 'admin'   ,
  create_time       date  ,
  update_by         varchar2(64)     default 'admin'  ,
  update_time       date             DEFAULT SYSDATE,
  primary key (role_id)              
)  ;

comment ON TABLE erp_role IS '角色信息表';
comment ON COLUMN erp_role.role_id IS '角色ID';
comment ON COLUMN erp_role.role_name IS'角色名称';
comment ON COLUMN erp_role.role_key IS'角色权限字符串';
comment ON COLUMN erp_role.status IS'角色状态（0正常 1停用）';
comment ON COLUMN erp_role.del_flag IS'删除标志（0代表存在 2代表删除）';
comment ON COLUMN erp_role.create_by IS'创建者';
comment ON COLUMN erp_role.create_time IS'创建时间';
comment ON COLUMN erp_role.update_by IS'更新者';
comment ON COLUMN erp_role.update_time IS'更新时间';



insert into erp_role(role_id,role_name,role_key) values(1,'超级管理员', 'admin' );
insert into erp_role(role_id,role_name,role_key,create_by) values(2, '普通角色',   'common', 'common');
insert into erp_role(role_id,role_name,role_key) values(3,'其他角色', 'other' );


COMMIT ;


SELECT * FROM erp_role;
